Skip Headers

Oracle® OLAP Reference 10g Release 2 (10.2) Part Number B14350-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Creating a Cube

Creating a cube entity is only the first step in constructing the OLAP Catalog metadata for a cube. Each cube must have at least one dimension and at least one measure. More typically, it will have multiple dimensions and multiple measures.

## Procedure: Create a Cube

The general steps for constructing a cube are as follows:

1. Follow the steps in "Procedure: Create an OLAP Dimension" for each of the cube's dimensions.

2. Call procedures in `CWM2_OLAP_CUBE` to create the cube and identify its dimensions.

3. Call procedures in `CWM2_OLAP_MEASURE` to create the cube's measures.

4. Call procedures in `CWM2_OLAP_TABLE_MAP` to map the cube's measures to columns in fact tables and to map foreign key columns in the fact tables to key columns in the dimension tables.

## Example: Create a Costs Cube

The PL/SQL statements in Example 2-3 create a logical `CWM2` cube object, `ANALYTIC_CUBE`, for the `COSTS` fact table in the `SH` schema. The dimensions of the cube are `PRODUCT_DIM`, shown in Example 2-1, and `TIME_DIM`, shown in Example 2-2.

The COSTS fact table has the following columns.

Column Name | Data Type
--- | ---
`PROD_ID` | `NUMBER`
`TIME_ID` | `DATE`
`UNIT_COST` | `NUMBER`
`UNIT_PRICE` | `NUMBER`

Example 2-3 Create an OLAP Cube for the COSTS Fact Table

    ---   CREATE THE ANALYTIC_CUBE CUBE  ---
    cwm2_olap_cube.create_cube('SH', 'ANALYTIC_CUBE', 'Analytics',
         'Analytic Cube','Unit Cost and Price Analysis');

    ---  ADD THE DIMENSIONS TO THE CUBE  ---
    cwm2_olap_cube.add_dimension_to_cube('SH', 'ANALYTIC_CUBE',
             'SH', 'TIME_DIM');
    cwm2_olap_cube.add_dimension_to_cube('SH', 'ANALYTIC_CUBE',
             'SH', 'PRODUCT_DIM');

    ---   CREATE THE MEASURES  ---
    cwm2_olap_measure.create_measure('SH', 'ANALYTIC_CUBE', 'UNIT_COST',
              'Unit Cost','Unit Cost', 'Unit Cost');
    cwm2_olap_measure.create_measure('SH', 'ANALYTIC_CUBE', 'UNIT_PRICE',
              'Unit Price','Unit Price', 'Unit Price');

    ---   CREATE THE MAPPINGS  ---
    cwm2_olap_table_map.Map_FactTbl_LevelKey
         ('SH', 'ANALYTIC_CUBE','SH', 'COSTS', 'LOWESTLEVEL',
          'DIM:SH.PRODUCTS/HIER:STANDARD/LVL:L4/COL:PROD_ID;
           DIM:SH.TIME/HIER:CALENDAR/LVL:L3/COL:MONTH;');
    cwm2_olap_table_map.Map_FactTbl_Measure
         ('SH', 'ANALYTIC_CUBE','UNIT_COST', 'SH', 'COSTS', 'UNIT_COST',
          'DIM:SH.PRODUCTS/HIER:STANDARD/LVL:L4/COL:PROD_ID;
           DIM:SH.TIME/HIER:CALENDAR/LVL:L3/COL:MONTH;');
    cwm2_olap_table_map.Map_FactTbl_Measure
         ('SH', 'ANALYTIC_CUBE','UNIT_PRICE', 'SH', 'COSTS', 'UNIT_PRICE',
          'DIM:SH.PRODUCTS/HIER:STANDARD/LVL:L4/COL:PROD_ID;
           DIM:SH.TIME/HIER:CALENDAR/LVL:L3/COL:MONTH;');